package su.msk.jet.team01.chatik.server.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;

public final class DatabaseAdapter {
	private static DatabaseAdapter database = null;
	private static String dbURL = "jdbc:derby://localhost:1527/jet-0002-team01;create=true;";

	private static Logger logger = LoggerFactory
			.getLogger(DatabaseAdapter.class);

	private static final int MIN_CONNECTIONS_PER_PARTITION = 5;
	private static final int MAX_CONNECTIONS_PER_PARTITION = 10;

	private static BoneCP connectionPool = null;

	public static DatabaseAdapter get() {
		if (database == null) {
			database = new DatabaseAdapter();
		}
		return database;
	}

	private DatabaseAdapter() {
		createConnectionPool();
	}

	public int execute(final String sql, final Object... args)
			throws SQLException {
		PreparedStatement stmt = null;
		int rowsModified = 0;
		Connection conn = null;

		try {
			conn = connectionPool.getConnection();
			stmt = conn.prepareStatement(sql);
			prepareStatement(stmt, args);
			rowsModified = stmt.executeUpdate();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException ex) {
					logger.error(ex.getMessage(), ex);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ex) {
					logger.error(ex.getMessage(), ex);
				}
			}
		}
		
		return rowsModified;
	}

	public List<Map<String, Object>> executeQuery(final String sql,
			final Object... args) throws SQLException {
		List<Map<String, Object>> table = null;
		PreparedStatement stmt = null;
		ResultSet data = null;
		Connection conn = null;
		
		try {
			conn = connectionPool.getConnection();
			stmt = conn.prepareStatement(sql);
			prepareStatement(stmt, args);
			data = stmt.executeQuery();
			table = DatabaseResultConverter.convertResults(data);
		} finally {
			if (data != null) {
				try {
					data.close();
				} catch (SQLException ex) {
					logger.error(ex.getMessage(), ex);
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException ex) {
					logger.error(ex.getMessage(), ex);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ex) {
					logger.error(ex.getMessage(), ex);
				}
			}
		}
		
		return table;
	}

	private void createConnectionPool() {

		try {
			Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
		} catch (Exception ex) {
			logger.error(ex.getMessage(), ex);
		}

		try {
			connectionPool = new BoneCP(createConnectionPoolConfig());
		} catch (SQLException ex) {
			logger.error(ex.getMessage(), ex);
		}

	}

	private BoneCPConfig createConnectionPoolConfig() {
		BoneCPConfig config;

		config = new BoneCPConfig();
		config.setJdbcUrl(dbURL);
		config.setMinConnectionsPerPartition(MIN_CONNECTIONS_PER_PARTITION);
		config.setMaxConnectionsPerPartition(MAX_CONNECTIONS_PER_PARTITION);
		config.setPartitionCount(1);

		return config;
	}

	private void prepareStatement(final PreparedStatement stmt,
			final Object... args) throws SQLException {
		for (int i = 0; i < args.length; i++) {
			stmt.setObject(i + 1, args[i]);
		}
	}

}
